package com.sukaiyi.generator.processor;

import cn.hutool.core.util.StrUtil;
import com.sukaiyi.generator.processor.exception.CodeGenerateException;
import com.sukaiyi.generator.processor.model.ClassInfo;
import com.sukaiyi.generator.processor.model.FieldInfo;
import com.sukaiyi.generator.utils.StringUtils;

import java.math.BigDecimal;
import java.time.Instant;
import java.time.LocalDateTime;
import java.util.*;
import java.util.function.UnaryOperator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author xuxueli 2018-05-02 21:10:45
 */
public class SqlParser {

    /**
     * 解析建表SQL生成代码（model-dao-xml）
     *
     * @param tableSql sql
     * @return java 对象
     */
    @Deprecated
    public static ClassInfo processTableIntoClassInfo(String tableSql) {
        if (tableSql == null || tableSql.trim().length() == 0) {
            throw new CodeGenerateException("Table structure can not be empty.");
        }
        tableSql = tableSql.trim();

        // table Name
        String tableName = null;
        if (tableSql.contains("TABLE") && tableSql.contains("(")) {
            tableName = tableSql.substring(tableSql.indexOf("TABLE") + 5, tableSql.indexOf("("));
        } else if (tableSql.contains("table") && tableSql.contains("(")) {
            tableName = tableSql.substring(tableSql.indexOf("table") + 5, tableSql.indexOf("("));
        } else {
            throw new CodeGenerateException("Table structure anomaly.");
        }

        if (tableName.contains("`")) {
            tableName = tableName.substring(tableName.indexOf("`") + 1, tableName.lastIndexOf("`"));
        }

        // class Name
        String className = StringUtils.upperCaseFirst(StringUtils.underlineToCamelCase(tableName));
        if (className.contains("_")) {
            className = className.replaceAll("_", "");
        }

        // class Comment
        String classComment = "";
        if (tableSql.contains("COMMENT=")) {
            String classCommentTmp = tableSql.substring(tableSql.lastIndexOf("COMMENT=") + 8).trim();
            if (classCommentTmp.contains("'") || classCommentTmp.indexOf("'") != classCommentTmp.lastIndexOf("'")) {
                classCommentTmp = classCommentTmp.substring(classCommentTmp.indexOf("'") + 1, classCommentTmp.lastIndexOf("'"));
            }
            if (classCommentTmp != null && classCommentTmp.trim().length() > 0) {
                classComment = classCommentTmp;
            }
        }

        // field List
        List<FieldInfo> fieldList = new ArrayList<>();

        String fieldListTmp = tableSql.substring(tableSql.indexOf("(") + 1, tableSql.lastIndexOf(")"));

        // replave "," by "，" in comment
        Matcher matcher = Pattern.compile("\\ COMMENT '(.*?)\\'").matcher(fieldListTmp);     // "\\{(.*?)\\}"
        while (matcher.find()) {

            String commentTmp = matcher.group();
            commentTmp = commentTmp.replaceAll("\\ COMMENT '|\\'", "");      // "\\{|\\}"

            if (commentTmp.contains(",")) {
                String commentTmpFinal = commentTmp.replaceAll(",", "，");
                fieldListTmp = fieldListTmp.replace(commentTmp, commentTmpFinal);
            }
        }

        // remove invalid data
        for (Pattern pattern : Arrays.asList(
                Pattern.compile("[\\s]*PRIMARY KEY .*(\\),|\\))"),      // remove PRIMARY KEY
                Pattern.compile("[\\s]*UNIQUE KEY .*(\\),|\\))"),       // remove UNIQUE KEY
                Pattern.compile("[\\s]*KEY .*(\\),|\\))")               // remove KEY
        )) {
            Matcher patternMatcher = pattern.matcher(fieldListTmp);
            while (patternMatcher.find()) {
                fieldListTmp = fieldListTmp.replace(patternMatcher.group(), "");
            }
        }

        String[] fieldLineList = fieldListTmp.split(",");
        if (fieldLineList.length > 0) {
            for (String columnLine : fieldLineList) {
                columnLine = columnLine.trim();                                                // `userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                if (columnLine.startsWith("`")) {

                    // column Name
                    columnLine = columnLine.substring(1);                                    // userid` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                    String columnName = columnLine.substring(0, columnLine.indexOf("`"));    // userid

                    // field Name
                    String fieldName = StringUtils.lowerCaseFirst(StringUtils.underlineToCamelCase(columnName));
                    if (fieldName.contains("_")) {
                        fieldName = fieldName.replaceAll("_", "");
                    }

                    // field class
                    columnLine = columnLine.substring(columnLine.indexOf("`") + 1).trim();    // int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
                    String fieldClass = Object.class.getSimpleName();
                    if (columnLine.startsWith("int") || columnLine.startsWith("tinyint") || columnLine.startsWith("smallint")) {
                        fieldClass = Integer.class.getName();
                    } else if (columnLine.startsWith("bigint")) {
                        fieldClass = Long.class.getName();
                    } else if (columnLine.startsWith("float")) {
                        fieldClass = Float.class.getName();
                    } else if (columnLine.startsWith("double")) {
                        fieldClass = Double.class.getName();
                    } else if (columnLine.startsWith("date") || columnLine.startsWith("timestamp")) {
                        fieldClass = Date.class.getName();
                    } else if (columnLine.startsWith("varchar") || columnLine.startsWith("text") || columnLine.startsWith("char")) {
                        fieldClass = String.class.getName();
                    } else if (columnLine.startsWith("decimal")) {
                        fieldClass = BigDecimal.class.getName();
                    }

                    // field comment
                    String fieldComment = "";
                    if (columnLine.contains("COMMENT")) {
                        String commentTmp = fieldComment = columnLine.substring(columnLine.indexOf("COMMENT") + 7).trim();    // '用户ID',
                        if (commentTmp.contains("'") || commentTmp.indexOf("'") != commentTmp.lastIndexOf("'")) {
                            commentTmp = commentTmp.substring(commentTmp.indexOf("'") + 1, commentTmp.lastIndexOf("'"));
                        }
                        fieldComment = commentTmp;
                    }

                    FieldInfo fieldInfo = new FieldInfo();
                    fieldInfo.setColumnName(columnName);
                    fieldInfo.setFieldName(fieldName);
                    fieldInfo.setFieldClass(fieldClass);
                    fieldInfo.setFieldComment(fieldComment);

                    fieldList.add(fieldInfo);
                }
            }
        }

        if (fieldList.size() < 1) {
            throw new CodeGenerateException("Table structure anomaly.");
        }

        ClassInfo codeJavaInfo = new ClassInfo();
        codeJavaInfo.setTableName(tableName);
        codeJavaInfo.setClassName(className);
        codeJavaInfo.setClassComment(classComment);
        codeJavaInfo.setFieldList(fieldList);

        return codeJavaInfo;
    }


    private static final UnaryOperator<String> REMOVE_BACK_QUOTE = string -> string == null ? null : string.replace("`", "");
    private static final UnaryOperator<String> REMOVE_FULL_NAME = string -> {
        if (string == null || string.isEmpty()) {
            return string;
        }
        int lastDot = string.lastIndexOf('.');
        return lastDot == -1 ? string : string.substring(lastDot + 1);
    };

    private static final UnaryOperator<String> CONVERT_UNDERLINE_TO_CAMEL_CASE = StrUtil::toCamelCase;
    private static final UnaryOperator<String> UPPER_FIRST_CHAR = StrUtil::upperFirst;

    private static final Map<String, String> SQL_JAVA_TYPE_MAP = new HashMap<String, String>() {{
        put("int", Integer.class.getName());
        put("tinyint", Integer.class.getName());
        put("smallint", Integer.class.getName());
        put("bigint", Long.class.getName());
        put("float", Float.class.getName());
        put("double", Double.class.getName());
        put("date", LocalDateTime.class.getName());
        put("timestamp", Instant.class.getName());
        put("varchar", String.class.getName());
        put("text", String.class.getName());
        put("char", String.class.getName());
        put("decimal", BigDecimal.class.getName());
    }};

    public static ClassInfo parse(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            throw new CodeGenerateException("Table structure can not be empty.");
        }
        String trimSql = sql.trim();
        int firstLeftBracket = trimSql.indexOf('(');
        String tableName = Optional.of(trimSql)
                .map(s -> s.substring("create table ".length(), firstLeftBracket))
                .map(REMOVE_BACK_QUOTE)
                .map(REMOVE_FULL_NAME)
                .map(String::trim)
                .orElse(null);
        if (tableName == null) {
            throw new CodeGenerateException("Table structure anomaly.");
        }
        String className = Optional.of(tableName)
                .map(CONVERT_UNDERLINE_TO_CAMEL_CASE)
                .map(UPPER_FIRST_CHAR)
                .orElse(tableName);
        int lastRightBracket = trimSql.lastIndexOf(')');
        int commentStartPos = trimSql.indexOf("COMMENT", lastRightBracket);
        if (commentStartPos == -1) {
            commentStartPos = trimSql.indexOf("comment", lastRightBracket);
        }
        String classComment = Optional.of(commentStartPos)
                .filter(e -> e >= 0)
                .map(e -> e + "COMMENT".length())
                .map(trimSql::substring)
                .map(String::trim)
                .map(s -> StrUtil.removePrefix(s, "\""))
                .map(s -> StrUtil.removePrefix(s, "'"))
                .map(s -> StrUtil.removePrefix(s, "='"))
                .map(s -> StrUtil.removeSuffix(s, ";"))
                .map(s -> StrUtil.removeSuffix(s, "'"))
                .map(s -> StrUtil.removeSuffix(s, "\""))
                .orElse(null);

        String fieldPartSql = trimSql.substring(firstLeftBracket + 1, lastRightBracket);
        String[] fieldPartArr = fieldPartSql.split(",\n");
        List<FieldInfo> fieldList = Arrays.stream(fieldPartArr)
                .filter(StrUtil::isNotEmpty)
                .map(SqlParser::parseFieldInfo)
                .filter(Objects::nonNull)
                .collect(Collectors.toList());

        ClassInfo classInfo = new ClassInfo();
        classInfo.setTableName(tableName);
        classInfo.setClassName(className);
        classInfo.setClassComment(classComment);
        classInfo.setFieldList(fieldList);
        return classInfo;
    }

    private static FieldInfo parseFieldInfo(String fieldPart) {
        if (fieldPart == null || fieldPart.isEmpty()) {
            return null;
        }
        String trimFieldPart = fieldPart.trim();

        int firstBlankPos = trimFieldPart.indexOf(" ");
        String columnName = Optional.of(trimFieldPart)
                .map(e -> e.substring(0, firstBlankPos))
                .map(REMOVE_BACK_QUOTE)
                .orElse(null);
        if (columnName == null) {
            return null;
        }
        String fieldName = CONVERT_UNDERLINE_TO_CAMEL_CASE.apply(columnName);

        String[] parts = trimFieldPart.split(" ");
        String fieldClass = Optional.of(parts)
                .filter(e -> e.length > 1)
                .map(e -> e[1])
                .map(String::toLowerCase)
                .flatMap(e -> SQL_JAVA_TYPE_MAP.keySet().stream().filter(e::startsWith).findFirst())
                .map(SQL_JAVA_TYPE_MAP::get)
                .orElse(null);
        if (fieldClass == null) {
            return null;
        }
        int commentStartIndex = trimFieldPart.indexOf("COMMENT");
        if (commentStartIndex == -1) {
            commentStartIndex = trimFieldPart.indexOf("comment");
        }
        int commentEndIndex = trimFieldPart.lastIndexOf("'");
        if (commentEndIndex == -1) {
            commentEndIndex = trimFieldPart.lastIndexOf("\"");
        }
        String fieldComment = "";
        if (commentStartIndex != -1 && commentEndIndex != -1) {
            fieldComment = trimFieldPart.substring(commentStartIndex + "COMMENT".length(), commentEndIndex);
            fieldComment = Optional.of(fieldComment)
                    .map(String::trim)
                    .map(s -> StrUtil.removePrefix(s, "\""))
                    .map(s -> StrUtil.removePrefix(s, "'"))
                    .map(s -> StrUtil.removePrefix(s, "='"))
                    .map(s -> StrUtil.removeSuffix(s, "'"))
                    .map(s -> StrUtil.removeSuffix(s, ";"))
                    .map(s -> StrUtil.removeSuffix(s, "\""))
                    .orElse("");
        }

        FieldInfo fieldInfo = new FieldInfo();
        fieldInfo.setColumnName(columnName);
        fieldInfo.setFieldName(fieldName);
        fieldInfo.setFieldClass(fieldClass);
        fieldInfo.setFieldComment(fieldComment);
        return fieldInfo;
    }

}
